package com.netcracker.training.automonic.dao.entities.oracle;

import static com.netcracker.training.automonic.dao.entities.oracle.SQLQueries.*;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.List;

import org.apache.log4j.Logger;

import com.netcracker.training.automonic.dao.entities.Attribute;
import com.netcracker.training.automonic.dao.entities.AttributeType;
import com.netcracker.training.automonic.dao.entities.Object;
import com.netcracker.training.automonic.dao.factories.oracle.OracleDAOFactory;

/**
 * Concrete implementation for Oracle Database Express Edition 11g Release 2.
 * 
 * @author Vadim Gidulyan
 * 
 * @see Attribute
 */

public class OracleAttribute implements Attribute // TODO Javadoc
{

	public static final Logger LOGGER = Logger.getLogger(OracleSite.class);
	
	private static OracleDAOFactory daoFactory = new OracleDAOFactory();
	private static Connection con = getConnection();
	
	private static Connection getConnection()
	{
		if (con == null)
			con = daoFactory.createConnection();
		return con;
	}
	
	public static Attribute createAttribute(Object object, 
			AttributeType attributeType)
	{
		Attribute attribute = null;
		
		try (Statement st = con.createStatement())
		{
			String sql = "SELECT seq_Attributes.NEXTVAL FROM DUAL";
			
			ResultSet rs = st.executeQuery(sql);
			
			if (rs.next())
			{
				int id = rs.getInt(1);
				attribute = new OracleAttribute(id, object, attributeType);
			}
			rs.close();
		}
		catch (SQLException e)
		{
			LOGGER.error(null, e);
			e.printStackTrace();
		}
		finally
		{
			if (LOGGER.isTraceEnabled())
				LOGGER.trace(attribute);
		}
		
		return attribute;
	}
	
	public static Attribute selectAttributeById(int id)
	{
		Attribute attribute = null;
		
		try (Statement stmt = con.createStatement())
		{
			String sql = String.format(SELECT_ATTRIBUTE_BY_ID, id);
			
			ResultSet rs = stmt.executeQuery(sql);
			
			if (rs.next())
			{
				Object object = 
						OracleObject.selectObjectById(rs.getInt("objectid"));
				
				AttributeType attributeType = 
						OracleAttributeType.selectAttributeTypeById(
								rs.getInt("attrtype"));
				
				attribute = new OracleAttribute(rs.getInt("attr_id"),
				                                object,
				                                attributeType);
				attribute.setValue(rs.getString("value"));
			}
			rs.close();
		}
		catch (SQLException e)
		{
			LOGGER.error(null, e);
			e.printStackTrace();
		}
		finally
		{
			if (LOGGER.isTraceEnabled())
				LOGGER.trace(attribute);
		}
		
		return attribute;
	}
	
	public static Attribute selectAttributesByObjectAndType(Object object, 
			AttributeType attributeType)
	{
		Attribute attribute = null;
		
		try (Statement stmt = con.createStatement())
		{
			String sql = String.format(SELECT_ATTRIBUTE_BY_OBJECT_TYPE, 
			                           object.getId(), attributeType.getId());
			
			ResultSet rs = stmt.executeQuery(sql);
			
			if (rs.next())
			{
				attribute = new OracleAttribute(rs.getInt("attr_id"),
				                                object,
				                                attributeType);
				attribute.setValue(rs.getString("value"));
			}
			rs.close();
		}
		catch (SQLException e)
		{
			LOGGER.error(null, e);
			e.printStackTrace();
		}
		finally
		{
			if (LOGGER.isTraceEnabled())
				LOGGER.trace(attribute);
		}
		
		return attribute;
	}
	
	public static List<Attribute> selectAttributesByObject(Object object)
	{
		List<Attribute> attributes = new LinkedList<Attribute>();
		
		try (Statement stmt = con.createStatement())
		{
			String sql = String.format(SELECT_ATTRIBUTES_BY_OBJECT_ID,
			                           object.getId());
			
			ResultSet rs = stmt.executeQuery(sql);
			
			while (rs.next())
			{
				AttributeType attributeType = 
						OracleAttributeType.selectAttributeTypeById(
								rs.getInt("attrtype"));
				
				Attribute attribute =
						new OracleAttribute(rs.getInt("attr_id"),
				                            object,
				                            attributeType);
				attribute.setValue(rs.getString("value"));
				attributes.add(attribute);
			}
			rs.close();
		}
		catch (SQLException e)
		{
			LOGGER.error(null, e);
			e.printStackTrace();
		}
		finally
		{
			if (LOGGER.isTraceEnabled())
				for (Attribute attribute : attributes)
					LOGGER.trace(attribute);
		}
		
		return attributes;
	}

	public static List<Attribute> selectAttributesByType(AttributeType attributeType)
	{
		List<Attribute> attributes = new LinkedList<Attribute>();
		
		try (Statement stmt = con.createStatement())
		{
			String sql = String.format(SELECT_ATTRIBUTES_BY_TYPE_ID,
			                           attributeType.getId());
			
			ResultSet rs = stmt.executeQuery(sql);
			
			while (rs.next())
			{
				Object object = 
						OracleObject.selectObjectById(rs.getInt("objectid"));
				
				Attribute attribute =
						new OracleAttribute(rs.getInt("attr_id"),
				                            object,
				                            attributeType);
				attribute.setValue(rs.getString("value"));
				attributes.add(attribute);
			}
			rs.close();
		}
		catch (SQLException e)
		{
			LOGGER.error(null, e);
			e.printStackTrace();
		}
		finally
		{
			if (LOGGER.isTraceEnabled())
				for (Attribute attribute : attributes)
					LOGGER.trace(attribute);
		}
		
		return attributes;
	}
	
	public static List<Attribute> selectAllAttributes()
	{
		List<Attribute> attributes = new LinkedList<Attribute>();
		
		try (Statement stmt = con.createStatement())
		{
			ResultSet rs = stmt.executeQuery(SELECT_ALL_ATTRIBUTES);
			
			while (rs.next())
			{
				Object object = 
						OracleObject.selectObjectById(rs.getInt("objectid"));
				
				AttributeType attributeType = 
						OracleAttributeType.selectAttributeTypeById(
								rs.getInt("attrtype"));
				
				Attribute attribute =
						new OracleAttribute(rs.getInt("attr_id"),
				                            object,
				                            attributeType);
				attribute.setValue(rs.getString("value"));
				attributes.add(attribute);
			}
			rs.close();
		}
		catch (SQLException e)
		{
			LOGGER.error(null, e);
			e.printStackTrace();
		}
		finally
		{
			if (LOGGER.isTraceEnabled())
				for (Attribute attribute : attributes)
					LOGGER.trace(attribute);
		}
		
		return attributes;
	}

	private final int id;
	private final Object object;
	private final AttributeType attributeType;
	private String value = "";
	
	private boolean modified;
	
	// CONSTRUCTORS
	
	protected OracleAttribute(int id,
			Object object, AttributeType attributeType)
	{
		this.id            = id;
		this.object        = object;
		this.attributeType = attributeType;
		this.value         = "";
		
		modified = false;
	}
	
	// ACCESSORS
	
	@Override
	public int getId()
	{
		return id;
	}

	@Override
	public Object getObject()
	{
		return object;
	}

	@Override
	public AttributeType getType()
	{
		return attributeType;
	}

	@Override
	public String getValue()
	{
		return value;
	}

	@Override
	public void setValue(String value)
	{
		if (this.value.hashCode() != value.hashCode())
			modified = true;
		value = value.replaceAll("'{1,}", "''");
		this.value = value;
	}
	
	// METHODS
	
	@Override
	public String toString()
	{
		return String.format("%d [%s] %s : %s",
		                     id, object.getName(), value, attributeType.getCode());
	}
	
	@Override
	public void save()
	{
		boolean attributeExists = (selectAttributeById(id) != null);
		
		if (attributeExists)
		{
			if (modified)
			{
				String sql = String.format(
						"UPDATE Attributes "
					  + "SET value = '%s' "
					  + "WHERE attr_id = %d",
						value, id);
				
				try (Statement st = con.createStatement())
				{
					st.executeUpdate(sql);
				}
				catch (SQLException e)
				{
					LOGGER.error(null, e);
					e.printStackTrace();
				}
				finally
				{
					if (LOGGER.isTraceEnabled())
						LOGGER.trace(sql);
				}
			}
		}
		else
		{
			String sql = String.format(
					"INSERT INTO Attributes "
				  + "	VALUES (%d, %d, %d, '%s')",
					id, object.getId(), attributeType.getId(), value);
			
			try (Statement st = con.createStatement())
			{
				st.executeUpdate(sql);
			}
			catch (SQLException e)
			{
				LOGGER.error(null, e);
				e.printStackTrace();
			}
			finally
			{
				if (LOGGER.isTraceEnabled())
					LOGGER.trace(sql);
			}
		}
		
		modified = false;
		LOGGER.info("Saved attribute: " + this);
	}

}
